/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package persistence;

import com.sun.rowset.CachedRowSetImpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;
import modele.Ville;
import persistence.DB;
import tools.ThreadsPool;

/**
 *
 * @author Val
 */
public class Villes {
    private static final String LISTER_VILLES = "SELECT nom_ville, nom_pays, latitude, longitude FROM VILLE;";
    private static final String INSERT_VILLE = "INSERT INTO VILLE(nom_ville,nom_pays,latitude,longitude) VALUES(?,?,?,?);";

    public static List<Ville> getVilles() throws SQLException {
        List<Ville> liste = new ArrayList<Ville>();

        CachedRowSet rs = new CachedRowSetImpl();
        rs.setCommand(LISTER_VILLES);
        Connection connection = DB.getConnection();
        try{
           rs.execute(connection); 
        }finally{
           connection.close();
        }

        while(rs.next()){
            Ville ville = new Ville(
                    rs.getString("nom_ville"), rs.getString("nom_pays"),
                    rs.getString("latitude"), rs.getString("longitude"));
            liste.add(ville);
        }
        return liste;
    }

    public static void addVilles(List<Ville> liste) throws SQLException {
        Connection connection = DB.getConnection();
        PreparedStatement st = null;
        try{
           for(Ville ville:liste){
            try{
                st = connection.prepareStatement(INSERT_VILLE);
                int i = 1;
                st.setString(i++, ville.getNom());
                st.setString(i++, ville.getPays());
                st.setString(i++, ville.getLat());
                st.setString(i++, ville.getLon());
                try {
                    st.executeUpdate();
                } catch (SQLException ex) {
                    Logger.getLogger(Villes.class.getName()).log(Level.SEVERE, null, ex);
                }
            }finally{
                if(st !=null) st.close();
            }
           }
        }finally{
           connection.close();
        }
    }

    public static void insertAsync(Ville ville) throws SQLException{
        ThreadsPool.getPool().submit(new InsertTask(ville));
    }

    public static void insertSync(Ville ville) throws SQLException {
        Connection connection = DB.getConnection();
            PreparedStatement st = null;
            try{
                st = connection.prepareStatement(INSERT_VILLE);
                int i = 1;
                st.setString(i++, ville.getNom());
                st.setString(i++, ville.getPays());
                st.setString(i++, ville.getLat());
                st.setString(i++, ville.getLon());
                st.executeUpdate();
            }finally{
               if(st !=null) st.close();
               connection.close();
            }
    }

    private static class InsertTask implements Runnable {
        private Ville ville;
        InsertTask(Ville ville) {this.ville = ville;}
        @Override
        public void run() {
            Connection connection = DB.getConnection();
            PreparedStatement st = null;
            try{
                try{
                    st = connection.prepareStatement(INSERT_VILLE);
                    int i = 1;
                    st.setString(i++, ville.getNom());
                    st.setString(i++, ville.getPays());
                    st.setString(i++, ville.getLat());
                    st.setString(i++, ville.getLon());
                    st.executeUpdate();
                }finally{
                   if(st !=null) st.close();
                   connection.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Villes.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }

}
